from flask import Blueprint, request, jsonify, send_file
from models import db, Certificate, Employee
from openpyxl import Workbook
from openpyxl.styles import Font, Alignment, PatternFill, Border, Side
from io import BytesIO
from datetime import datetime, date
from openpyxl.utils import get_column_letter

certificate_bp = Blueprint('certificate', __name__)

def get_common_styles():
    header_font = Font(bold=True, color="FFFFFF")
    header_fill = PatternFill(start_color="4472C4", end_color="4472C4", fill_type="solid")
    centered_alignment = Alignment(horizontal="center", vertical="center")
    thin_border = Border(left=Side(style='thin'), right=Side(style='thin'), top=Side(style='thin'), bottom=Side(style='thin'))

    header_style = {
        'font': header_font,
        'fill': header_fill,
        'alignment': centered_alignment,
        'border': thin_border
    }
    content_style = {
        'alignment': centered_alignment,
        'border': thin_border
    }
    return header_style, content_style

def adjust_column_width(sheet, data):
    # 通过迭代行和单元格来获取每一列的数据
    for col_idx in range(1, sheet.max_column + 1):
        max_length = 0
        # 迭代当前列的所有单元格
        for row in sheet.iter_rows(min_row=1, max_row=sheet.max_row, min_col=col_idx, max_col=col_idx):
            cell = row[0] # iter_rows returns a tuple of cells for each row
            try:
                value = cell.value
                if value is None:
                    cell_value = ''
                elif isinstance(value, (datetime, date)):
                     cell_value = value.strftime('%Y-%m-%d')
                else:
                     cell_value = str(value)

                # 估算中文字符宽度
                # 确保 cell_value 是字符串类型
                if not isinstance(cell_value, str):
                     cell_value = str(cell_value) if cell_value is not None else ''
                     
                chinese_chars = sum([1 for char in cell_value if '\u4e00' <= char <= '\u9fff'])
                english_chars = len(cell_value) - chinese_chars
                # 假设一个中文字符宽度约等于两个英文字符
                length = english_chars + chinese_chars * 2

                if length > max_length:
                    max_length = length
            except Exception as e:
                # 使用 cell.coordinate 获取单元格坐标
                # cell_coord = cell.coordinate if hasattr(cell, 'coordinate') else f"Column {col_idx}, Row {cell.row}"
                # print(f"Error processing cell value for column width: {cell_coord}, value: {cell.value}, error: {e}")
                # 继续处理下一个单元格，避免中断导出
                continue

        adjusted_width = (max_length + 2) * 1.2 # Add a little padding
        # 防止列宽过大或过小
        if adjusted_width > 100:
            adjusted_width = 100
        elif adjusted_width < 5:
             adjusted_width = 5

        # 使用列的字母表示来设置列宽
        column_letter = get_column_letter(col_idx)
        sheet.column_dimensions[column_letter].width = adjusted_width

@certificate_bp.route('/api/certificates/export/excel', methods=['GET'])
def export_certificates_excel():
    certificates = Certificate.query.all()

    # Define fields to export based on the rule, excluding id, created_at, updated_at
    fields = [
        'employee_name',
        'name',
        'certificate_no',
        'level',
        'issuing_org',
        'issue_date',
        'expiry_date',
        'description',
    ]

    # Define headers in Chinese
    headers = {
        'employee_name': '员工姓名',
        'name': '证书名称',
        'certificate_no': '证书编号',
        'level': '级别',
        'issuing_org': '颁发机构',
        'issue_date': '颁发日期',
        'expiry_date': '有效期至',
        'description': '描述',
    }

    workbook = Workbook()
    sheet = workbook.active
    sheet.title = "证书列表"

    header_style, content_style = get_common_styles()

    # Write headers
    header_row = [headers[field] for field in fields]
    sheet.append(header_row)
    for col_num, cell in enumerate(sheet[1]):
        cell.font = header_style['font']
        cell.fill = header_style['fill']
        cell.alignment = header_style['alignment']
        cell.border = header_style['border']

    # Write data rows
    for certificate in certificates:
        row_data = []
        certificate_dict = certificate.to_dict() # Use the existing to_dict method
        for field in fields:
            value = certificate_dict.get(field)
            # Format date fields
            if isinstance(value, str):
                try:
                    # Attempt to parse ISO format date/datetime strings
                    if 'T' in value:
                         value = datetime.fromisoformat(value)
                    else:
                         value = date.fromisoformat(value)
                except ValueError:
                     pass # Keep original string if parsing fails

            row_data.append(value)

        sheet.append(row_data)
        # Apply content style to the newly added row
        for cell in sheet[sheet.max_row]:
             cell.alignment = content_style['alignment']
             cell.border = content_style['border']

    # Adjust column widths
    adjust_column_width(sheet, certificates)

    # Save to a byte stream
    excel_buffer = BytesIO()
    workbook.save(excel_buffer)
    excel_buffer.seek(0)

    return send_file(
        excel_buffer,
        as_attachment=True,
        download_name='证书列表.xlsx',
        mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
    )

@certificate_bp.route('/api/employees/<int:employee_id>/certificates', methods=['GET'])
def get_certificates(employee_id):
    certificates = Certificate.query.filter_by(employee_id=employee_id).all()
    return jsonify([certificate.to_dict() for certificate in certificates])

@certificate_bp.route('/api/employees/<int:employee_id>/certificates', methods=['POST'])
def create_certificate(employee_id):
    data = request.get_json()
    data['employee_id'] = employee_id
    certificate = Certificate(**data)
    db.session.add(certificate)
    db.session.commit()
    return jsonify({'message': '创建成功', 'id': certificate.id}), 201

@certificate_bp.route('/api/employees/<int:employee_id>/certificates/<int:id>', methods=['PUT'])
def update_certificate(employee_id, id):
    certificate = Certificate.query.filter_by(id=id, employee_id=employee_id).first_or_404()
    data = request.get_json()
    for key, value in data.items():
        setattr(certificate, key, value)
    db.session.commit()
    return jsonify({'message': '修改成功'})

@certificate_bp.route('/api/employees/<int:employee_id>/certificates/<int:id>', methods=['DELETE'])
def delete_certificate(employee_id, id):
    certificate = Certificate.query.filter_by(id=id, employee_id=employee_id).first_or_404()
    db.session.delete(certificate)
    db.session.commit()
    return jsonify({'message': '删除成功'}) 